const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const ANALYSIS_RESULT_PATH = path.join(process.cwd(), 'scripts', 'instance-table-analysis-result.json');

console.log('🔧 更新87个实例表为整合式前缀命名结构');
console.log('=========================================');

try {
  // 1. 读取分析结果
  if (!fs.existsSync(ANALYSIS_RESULT_PATH)) {
    console.log('❌ 分析结果文件不存在，请先运行分析脚本');
    return;
  }
  
  const analysisResult = JSON.parse(fs.readFileSync(ANALYSIS_RESULT_PATH, 'utf8'));
  console.log('✅ 成功读取分析结果');
  
  const db = new Database(DB_PATH);
  
  console.log(`\n📋 开始更新 ${analysisResult.totalInstanceTables} 个实例表结构`);
  console.log('==================================================');
  
  let updatedTables = 0;
  let errorCount = 0;
  const updateErrors = [];
  
  // 2. 遍历每个属性组合并更新对应的实例表
  for (const [combinationKey, combination] of Object.entries(analysisResult.attributeCombinations)) {
    console.log(`\n🔧 处理组合: ${combination.namingTable} + ${combination.positionTable} + ${combination.baseParamsTable}`);
    console.log(`📊 包含 ${combination.tables.length} 个实例表`);
    
    // 为这个组合生成完整的字段列表
    const allFields = [];
    
    // 添加基础字段
    allFields.push('id INTEGER PRIMARY KEY AUTOINCREMENT');
    allFields.push('device_type_code TEXT NOT NULL');
    allFields.push('instance_number TEXT NOT NULL');
    allFields.push('object_number TEXT NOT NULL');
    allFields.push('object_description TEXT');
    allFields.push('created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP');
    allFields.push('updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP');
    
    // 添加命名字段 (table_c2)
    if (analysisResult.coreTableFields['table_c2']) {
      for (const field of analysisResult.coreTableFields['table_c2']) {
        const fieldName = `naming_${field}`;
        allFields.push(`[${fieldName}] TEXT`);
      }
    }
    
    // 添加位置字段
    const positionTable = combination.positionTable;
    if (positionTable && positionTable !== '-' && positionTable !== 'null') {
      const positionFieldKey = positionTable === 'table_c4' ? 'table_c4' : 'table_c6';
      if (analysisResult.coreTableFields[positionFieldKey]) {
        for (const field of analysisResult.coreTableFields[positionFieldKey]) {
          const fieldName = `position_${field}`;
          allFields.push(`[${fieldName}] TEXT`);
        }
      }
    }
    
    // 添加基础参数字段
    const baseParamsTable = combination.baseParamsTable;
    if (baseParamsTable && baseParamsTable !== '-' && baseParamsTable !== 'null') {
      try {
        const baseParamsFields = db.prepare(`PRAGMA table_info([${baseParamsTable}])`).all();
        const baseFieldNames = baseParamsFields.filter(f => 
          !['id', 'created_at', 'updated_at'].includes(f.name)
        );
        
        for (const field of baseFieldNames) {
          const fieldName = `base_params_${field.name}`;
          allFields.push(`[${fieldName}] TEXT`);
        }
      } catch (error) {
        console.log(`  ⚠️  无法获取 ${baseParamsTable} 字段信息: ${error.message}`);
      }
    }
    
    // 添加唯一约束
    allFields.push('UNIQUE(device_type_code, instance_number)');
    
    // 3. 更新每个实例表
    for (const tableMapping of combination.tables) {
      const tableName = tableMapping.instanceTable;
      
      try {
        console.log(`\n  🔧 更新表: ${tableName}`);
        
        // 备份现有数据
        let existingData = [];
        try {
          existingData = db.prepare(`SELECT * FROM [${tableName}]`).all();
          console.log(`    💾 备份了 ${existingData.length} 条现有数据`);
        } catch (error) {
          console.log(`    ℹ️  表可能不存在或为空: ${error.message}`);
        }
        
        // 删除旧表
        db.prepare(`DROP TABLE IF EXISTS [${tableName}]`).run();
        
        // 创建新表结构
        const createTableSQL = `
          CREATE TABLE [${tableName}] (
            ${allFields.join(',\n            ')}
          )
        `;
        
        db.prepare(createTableSQL).run();
        console.log(`    ✅ 创建新表结构 (${allFields.length - 1} 个字段)`);
        
        // 恢复数据（如果有的话）
        if (existingData.length > 0) {
          console.log(`    🔄 尝试恢复 ${existingData.length} 条数据...`);
          
          // 获取新表的列信息
          const newTableColumns = db.prepare(`PRAGMA table_info([${tableName}])`).all();
          const newColumnNames = newTableColumns.map(col => col.name);
          
          let restoredCount = 0;
          for (const row of existingData) {
            try {
              // 只选择新表中存在的字段
              const validData = {};
              for (const [key, value] of Object.entries(row)) {
                if (newColumnNames.includes(key)) {
                  validData[key] = value;
                }
              }
              
              if (Object.keys(validData).length > 0) {
                const columns = Object.keys(validData);
                const values = Object.values(validData);
                const placeholders = columns.map(() => '?').join(', ');
                const columnList = columns.map(col => `[${col}]`).join(', ');
                
                db.prepare(`
                  INSERT INTO [${tableName}] (${columnList}) 
                  VALUES (${placeholders})
                `).run(...values);
                
                restoredCount++;
              }
            } catch (insertError) {
              if (!insertError.message.includes('UNIQUE constraint failed')) {
                console.log(`      ⚠️  恢复数据失败: ${insertError.message}`);
              }
            }
          }
          
          console.log(`    ✅ 成功恢复 ${restoredCount}/${existingData.length} 条数据`);
        }
        
        // 验证新表结构
        const newTableInfo = db.prepare(`PRAGMA table_info([${tableName}])`).all();
        const newFieldCount = newTableInfo.length;
        
        console.log(`    📊 新表字段总数: ${newFieldCount} 个`);
        console.log(`    📋 命名字段: ${newTableInfo.filter(f => f.name.startsWith('naming_')).length} 个`);
        console.log(`    📋 位置字段: ${newTableInfo.filter(f => f.name.startsWith('position_')).length} 个`);
        console.log(`    📋 参数字段: ${newTableInfo.filter(f => f.name.startsWith('base_params_')).length} 个`);
        
        updatedTables++;
        
      } catch (error) {
        errorCount++;
        const errorMsg = `更新表 ${tableName} 失败: ${error.message}`;
        updateErrors.push(errorMsg);
        console.log(`    ❌ ${errorMsg}`);
      }
    }
    
    console.log(`  📊 该组合处理完成: ${combination.tables.length} 个表`);
  }
  
  // 4. 最终验证和统计
  console.log('\n📊 更新操作汇总');
  console.log('===================');
  console.log(`✅ 成功更新表数量: ${updatedTables} 个`);
  console.log(`❌ 更新失败数量: ${errorCount} 个`);
  
  if (updateErrors.length > 0) {
    console.log(`\n❌ 更新错误详情:`);
    updateErrors.forEach((error, index) => {
      console.log(`  ${index + 1}. ${error}`);
    });
  }
  
  // 验证最终结果
  console.log('\n🔍 验证更新结果...');
  
  const finalInstanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📋 当前实例表总数: ${finalInstanceTables.length} 个`);
  
  // 检查每个表的结构
  let integratedTables = 0;
  let totalRecords = 0;
  
  for (const table of finalInstanceTables) {
    try {
      const tableInfo = db.prepare(`PRAGMA table_info([${table.name}])`).all();
      const fieldNames = tableInfo.map(f => f.name);
      
      const hasNamingFields = fieldNames.some(f => f.startsWith('naming_'));
      const hasPositionFields = fieldNames.some(f => f.startsWith('position_'));
      const hasBaseParamsFields = fieldNames.some(f => f.startsWith('base_params_'));
      
      if (hasNamingFields || hasPositionFields || hasBaseParamsFields) {
        integratedTables++;
        
        const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
        totalRecords += count.count;
        
        if (count.count > 0) {
          console.log(`  📊 ${table.name}: ${tableInfo.length} 字段, ${count.count} 条记录 (已整合)`);
        }
      }
    } catch (error) {
      console.log(`  ❌ 检查 ${table.name} 失败: ${error.message}`);
    }
  }
  
  console.log(`\n🎉 更新完成！`);
  console.log(`📊 整合式结构表: ${integratedTables}/${finalInstanceTables.length} 个`);
  console.log(`💾 保留的数据记录: ${totalRecords} 条`);
  
  if (integratedTables === finalInstanceTables.length) {
    console.log(`\n✅ 所有87个实例表已成功更新为整合式前缀命名结构！`);
    console.log(`💡 现在每个实例表都包含了完整的属性字段，无需再使用record_id外键`);
  } else {
    console.log(`\n⚠️  还有 ${finalInstanceTables.length - integratedTables} 个表未完成整合`);
  }
  
  db.close();
  
} catch (error) {
  console.error('❌ 更新失败:', error.message);
  console.error(error.stack);
}